
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 11/04/2012 00:47:54
-- Generated from EDMX file: C:\Users\Женя\Desktop\library-web-project\ library-web-project\DataProject\EntityModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [sitecoreDb];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_BookAuthor_Book]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BookAuthor] DROP CONSTRAINT [FK_BookAuthor_Book];
GO
IF OBJECT_ID(N'[dbo].[FK_BookAuthor_Author]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BookAuthor] DROP CONSTRAINT [FK_BookAuthor_Author];
GO
IF OBJECT_ID(N'[dbo].[FK_BookPeriod]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PeriodSet] DROP CONSTRAINT [FK_BookPeriod];
GO
IF OBJECT_ID(N'[dbo].[FK_BookClient_Book]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BookClient] DROP CONSTRAINT [FK_BookClient_Book];
GO
IF OBJECT_ID(N'[dbo].[FK_BookClient_Client]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BookClient] DROP CONSTRAINT [FK_BookClient_Client];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[BookSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BookSet];
GO
IF OBJECT_ID(N'[dbo].[AuthorSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AuthorSet];
GO
IF OBJECT_ID(N'[dbo].[PeriodSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PeriodSet];
GO
IF OBJECT_ID(N'[dbo].[ClientSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ClientSet];
GO
IF OBJECT_ID(N'[dbo].[BookAuthor]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BookAuthor];
GO
IF OBJECT_ID(N'[dbo].[BookClient]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BookClient];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'BookSet'
CREATE TABLE [dbo].[BookSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Year] int  NOT NULL,
    [Description] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'AuthorSet'
CREATE TABLE [dbo].[AuthorSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FName] nvarchar(max)  NOT NULL,
    [LName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PeriodSet'
CREATE TABLE [dbo].[PeriodSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [BookId] int  NOT NULL,
    [BeginDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL
);
GO

-- Creating table 'ClientSet'
CREATE TABLE [dbo].[ClientSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FName] nvarchar(max)  NOT NULL,
    [LName] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [Adress] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'BookAuthor'
CREATE TABLE [dbo].[BookAuthor] (
    [Book_Id] int  NOT NULL,
    [Author_Id] int  NOT NULL
);
GO

-- Creating table 'BookClient'
CREATE TABLE [dbo].[BookClient] (
    [Book_Id] int  NOT NULL,
    [Client_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'BookSet'
ALTER TABLE [dbo].[BookSet]
ADD CONSTRAINT [PK_BookSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AuthorSet'
ALTER TABLE [dbo].[AuthorSet]
ADD CONSTRAINT [PK_AuthorSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PeriodSet'
ALTER TABLE [dbo].[PeriodSet]
ADD CONSTRAINT [PK_PeriodSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ClientSet'
ALTER TABLE [dbo].[ClientSet]
ADD CONSTRAINT [PK_ClientSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Book_Id], [Author_Id] in table 'BookAuthor'
ALTER TABLE [dbo].[BookAuthor]
ADD CONSTRAINT [PK_BookAuthor]
    PRIMARY KEY NONCLUSTERED ([Book_Id], [Author_Id] ASC);
GO

-- Creating primary key on [Book_Id], [Client_Id] in table 'BookClient'
ALTER TABLE [dbo].[BookClient]
ADD CONSTRAINT [PK_BookClient]
    PRIMARY KEY NONCLUSTERED ([Book_Id], [Client_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Book_Id] in table 'BookAuthor'
ALTER TABLE [dbo].[BookAuthor]
ADD CONSTRAINT [FK_BookAuthor_Book]
    FOREIGN KEY ([Book_Id])
    REFERENCES [dbo].[BookSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Author_Id] in table 'BookAuthor'
ALTER TABLE [dbo].[BookAuthor]
ADD CONSTRAINT [FK_BookAuthor_Author]
    FOREIGN KEY ([Author_Id])
    REFERENCES [dbo].[AuthorSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BookAuthor_Author'
CREATE INDEX [IX_FK_BookAuthor_Author]
ON [dbo].[BookAuthor]
    ([Author_Id]);
GO

-- Creating foreign key on [BookId] in table 'PeriodSet'
ALTER TABLE [dbo].[PeriodSet]
ADD CONSTRAINT [FK_BookPeriod]
    FOREIGN KEY ([BookId])
    REFERENCES [dbo].[BookSet]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BookPeriod'
CREATE INDEX [IX_FK_BookPeriod]
ON [dbo].[PeriodSet]
    ([BookId]);
GO

-- Creating foreign key on [Book_Id] in table 'BookClient'
ALTER TABLE [dbo].[BookClient]
ADD CONSTRAINT [FK_BookClient_Book]
    FOREIGN KEY ([Book_Id])
    REFERENCES [dbo].[BookSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Client_Id] in table 'BookClient'
ALTER TABLE [dbo].[BookClient]
ADD CONSTRAINT [FK_BookClient_Client]
    FOREIGN KEY ([Client_Id])
    REFERENCES [dbo].[ClientSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BookClient_Client'
CREATE INDEX [IX_FK_BookClient_Client]
ON [dbo].[BookClient]
    ([Client_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------